﻿
using System;
using System.Data;
using System.Data.SqlClient;

namespace YNCNumberSys.db
{
    public class userDal
    {
        private CommandManager commandManager = new CommandManager();
        private ConnectionManager connectionManager = new ConnectionManager();
        /// <summary>
        /// 插入用户，插入前判断用户是否存在。
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="pwd"></param>
        /// <returns>false 用户存在;true 插入成功.</returns>
        public bool Insert(string userCode, string pwd,string username)
        {
            string insertSql = string.Format(
                @"INSERT INTO [YNCNumberSys].[dbo].[用户]
                           ([用户名]
                           ,[姓名]
                           ,[密码])
                     VALUES
                           ('{0}'
                           ,'{1}'
                           ,'{2}'); ", userCode, username, pwd);
            
            string selectSQL = string.Empty;
            selectSQL = string.Format(@"if NOT exists(SELECT * FROM [YNCNumberSys].[dbo].[用户] WHERE  [姓名]='{0}' or 用户名='{1}') ", username, userCode);
            string strSql = selectSQL + " " + insertSql;
            int row= commandManager.ExecuteScalar(connectionManager.getConnection(), strSql);
            return row == 1 ? true : false;
        }
        /// <summary>
        /// 是否是用户。
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public DataTable GetAllUser()
        {
            string sql = string.Format("select * from 用户  order by [ID];");
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql, "用户");
            return dt;
        }
        /// <summary>
        /// 删除用户。
        /// </summary>
        /// <param name="userCode"> 用户名（号）</param>
        /// <returns></returns>
        public int DelUser(string userCode)
        {
            string sql = string.Format("delete from 用户 where 用户名='{0}';", userCode);
            return commandManager.ExecuteScalar(connectionManager.getConnection(), sql);
        }
        /// <summary>
        /// 是否是用户。
        /// </summary>
        /// <param name="userCode"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool IsUser(string userCode,string pwd)
        {
            bool rt = false;
            string sql = string.Format("select * from [YNCNumberSys].[dbo].用户 where 用户名='{0}' and 密码='{1}';", userCode, pwd);
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql, "用户");
            if (dt != null)
                rt = dt.Rows.Count == 1 ? true : false;
            return rt;
        }

        /// <summary>
        /// 通过用户号获取用户实体
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public UserEntity GetUserByUserCode(string user)
        {
            UserEntity UserEntity = null;
            string sql = string.Format("select 用户名,姓名,ID,密码 from 用户 where 用户名='{0}';", user);
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql, "用户");
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    UserEntity = new UserEntity();
                    UserEntity.Id = (int)dt.Rows[0]["ID"];
                    UserEntity.Pwd = (string)dt.Rows[0]["密码"];
                    UserEntity.Name = (string)dt.Rows[0]["姓名"];
                    UserEntity.Code = (string)dt.Rows[0]["用户名"];
                }
            }
            return UserEntity;
        }

        /// <summary>
        /// 通过用户ID获取用户实体。
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public UserEntity GetUserByUserId(int userID)
        {
            UserEntity UserEntity = null;
            string sql = string.Format("select 用户名,姓名,ID,密码 from 用户 where ID={0};", userID);
            DataTable   dt= commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql, "用户");

            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    UserEntity = new UserEntity();
                    UserEntity.Id = (int)dt.Rows[0]["ID"];
                    UserEntity.Pwd = (string)dt.Rows[0]["密码"];
                    UserEntity.Name = (string)dt.Rows[0]["姓名"];
                    UserEntity.Code = (string)dt.Rows[0]["用户名"];
                }
            }
           return  UserEntity;
        }

        /// <summary>
        /// 通过用户ID获取用户实体。
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public int UpdatePwd(int userID,string newPwd)
        {
            string sql = string.Format("update 用户 set 密码 = '{0}' where ID={1}", newPwd, userID); ;
            return commandManager.update(connectionManager.getConnection(), sql);
        }
        

    }
}
